﻿Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Data

Public Class GDataConnection

    Private m_Conn As SqlConnection

    Private mstrErrorMess As String

    Private _connStr As String

    Private Property ConnStr() As String
        Get
            Return _connStr
        End Get
        Set(ByVal value As String)
            _connStr = value
        End Set
    End Property

    Public Property Conn() As SqlConnection
        Get
            Return m_Conn
        End Get
        Set(ByVal value As SqlConnection)
            m_Conn = value
        End Set
    End Property

    Public ReadOnly Property ErrorMessage() As String
        Get
            Return mstrErrorMess
        End Get
    End Property


    Public Sub New(ByVal conStr As String)
        Try
            m_Conn = New SqlConnection(conStr)
            m_Conn.Open()
        Catch ex As Exception
            mstrErrorMess = ex.Message
            Throw (New Exception("Connection to database server failed."))
        End Try
    End Sub

    Public Sub Close()
        If (m_Conn.State = ConnectionState.Open) Then
            m_Conn.Close()
            m_Conn.Dispose()
        End If
    End Sub

    Public Function ExecuteScalar(ByVal strSql As String) As Object
        Dim objReturn As Object = Nothing
        Try
            Dim command As New SqlCommand(strSql, m_Conn)
            objReturn = command.ExecuteScalar()
            If objReturn Is Nothing Then
                objReturn = "null"
            End If

            command.Dispose()
        Catch ex As Exception
            mstrErrorMess = ex.Message
            Return Nothing
        End Try
        Return objReturn
    End Function

    Public Function GetDataTable(ByVal strSql As String) As DataTable

        Dim da As SqlDataAdapter = Nothing
        Dim tTable As New DataTable()

        Try
            da = New SqlDataAdapter(strSql, m_Conn)

            da.Fill(tTable)
        Catch ex As Exception
            mstrErrorMess = ex.Message
            Return Nothing
        Finally
            da.Dispose()
        End Try
        Return tTable
    End Function



    Public Function ExecuteNoneQuery(ByVal strSql As String) As Boolean
        Try
            Dim command As New SqlCommand(strSql, m_Conn)
            command.ExecuteNonQuery()
            command.Dispose()
        Catch ex As Exception
            mstrErrorMess = ex.Message
            Return False
        End Try
        Return True
    End Function

    Public Function ExecuteNoneQueryWithResult(ByVal strSql As String) As Integer
        Dim mintResult As Integer
        Try
            Dim command As New SqlCommand(strSql, m_Conn)
            mintResult = command.ExecuteNonQuery()
            command.Dispose()
        Catch ex As Exception
            mstrErrorMess = ex.Message
            Return -1
        End Try
        Return mintResult
    End Function

    Public Function ExecuteCommandText(ByVal strSQL As String, ByVal paraList As SqlParameter()) As Object
        'Dim cmd As SqlCommand = Nothing
        Try
            Using cmd As New SqlCommand(strSQL, m_Conn)

                For Each para As SqlParameter In paraList ''# I had to guess at this type
                    cmd.Parameters.Add(para)
                Next

                cmd.ExecuteScalar()
            End Using

        Catch ex As Exception
            mstrErrorMess = ex.Message
            Return False
        Finally
            'cmd.Dispose()
        End Try

        Return True
    End Function

    Public Function ExecuteStoreProcedure(ByVal strStoreProcName As String, ByVal paraList As SqlParameter()) As Boolean
        Dim sqlCMD As SqlCommand = Nothing

        Try
            sqlCMD = New SqlCommand(strStoreProcName, m_Conn)

            sqlCMD.CommandType = CommandType.StoredProcedure
            For Each para As SqlParameter In paraList
                sqlCMD.Parameters.Add(para)
            Next
            sqlCMD.ExecuteNonQuery()
        Catch ex As Exception
            mstrErrorMess = ex.Message
            Return False
        Finally
            sqlCMD.Dispose()
        End Try

        Return True
    End Function

    Public Function ExecuteStoreProcedureReturnTable(ByVal strStoreProcName As String, ByVal paraList As SqlParameter()) As DataTable
        Dim sqlCMD As SqlCommand = Nothing

        Try
            sqlCMD = New SqlCommand(strStoreProcName, m_Conn)
            sqlCMD.CommandType = CommandType.StoredProcedure
            For Each para As SqlParameter In paraList
                sqlCMD.Parameters.Add(para)
            Next

            Dim DAdpt As New SqlDataAdapter(sqlCMD)
            Dim ds As New DataSet()
            DAdpt.Fill(ds, "table")
            Return ds.Tables("table")
        Catch ex As Exception
            mstrErrorMess = ex.Message
            Return Nothing
        End Try
    End Function

End Class
